package com.ruoyi.common.utils.mysql;


import java.sql.*;
import java.util.*;

/**
 * @author jw
 */
public class MysqlLink {

    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";


    public static List getAllDatabase(String ip, String port, String root, String password, String database) {
        String url = "jdbc:mysql://" + ip + ":" + port + "/information_schema?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection conn = null;
        Statement stmt = null;

        //存放所有数据库
        List<String> databases = new ArrayList<>();
        //存放所有表名
        List<Map<String, Object>> tableNames = new ArrayList<Map<String, Object>>();

        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(url, root, password);

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
            String sql;
            //获取所有数据库
            if (Objects.equals(database, "1")) {

                sql = "SHOW DATABASES";
                ResultSet rs = stmt.executeQuery(sql);
                // 展开结果集数据库
                while (rs.next()) {
                    // 通过字段检索
                    databases.add(rs.getString("database"));
                }
                // 完成后关闭
                rs.close();
                stmt.close();
                conn.close();
                return databases;
            } else {

                //获取选中数据库的所有表名与字段

                //获取数据库的元数据
                DatabaseMetaData dbMetaData = conn.getMetaData();
                //从元数据中获取到所有的表名
                ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});


                while (rs.next()) {
                    if (Objects.equals(rs.getString("TABLE_CAT"), database)) {
                        System.out.println("表名: " + rs.getString("TABLE_NAME"));
                        System.out.println("表类型: " + rs.getString("TABLE_TYPE"));
                        System.out.println("表所属数据库: " + rs.getString("TABLE_CAT"));
                        System.out.println("表所属用户名: " + rs.getString("TABLE_SCHEM"));
                        System.out.println("表备注: " + rs.getString("REMARKS"));

                        Map<String, Object> map = new HashMap<String, Object>();
                        map.put("name", rs.getString("TABLE_NAME"));
                        map.put("remarks", rs.getString("REMARKS"));
                        map.put("type", rs.getString("TABLE_TYPE"));
                        map.put("belong", rs.getString("TABLE_CAT"));
                        tableNames.add(map);
                    }

                }

                // 完成后关闭
                rs.close();
                stmt.close();
                conn.close();
                return tableNames;
            }

        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
            return null ;
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
            return null;
        } finally {
            // 关闭资源
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ignored) {
            }// 什么都不做
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
            System.out.println("Goodbye!");
        }
    }

    //查询每个表的字段
    public static List getAllField (String ip, String port, String root, String password, String database,String table) {
        String url = "jdbc:mysql://" + ip + ":" + port + "/" + database + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection conn = null;
        Statement stmt = null;
        //存放当前表的字段
        List<Map<String,Object>> tableFields = new ArrayList<>();

        //存放当前表的字段
        List<String> fields = new ArrayList<>();
        //存放当前表的字段类型
        List<String> fieldstype = new ArrayList<>();


        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(url, root, password);

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();

            //查询表的字段
            String sql = "select * from " + table;
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rstable = ps.executeQuery();
            //结果集元数据
            ResultSetMetaData meta = rstable.getMetaData();
            //表列数量
            int columeCount = meta.getColumnCount();
            for (int i = 1; i <= columeCount; i++) {
                fields.add(meta.getColumnName(i));
                fieldstype.add(meta.getColumnTypeName(i));
            }

            for (int j = 0; j<fields.size(); j++){
                //存放当前表的字段类型
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("name",fields.get(j));
                map.put("type",fieldstype.get(j));
                tableFields.add(map);
            }

            return tableFields;
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
            return null;
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
            return null;
        } finally {
            // 关闭资源
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ignored) {
            }// 什么都不做
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }

    }
    //查询选中数据库所有表信息
}
